"""
    python 操作 Excel
"""
from datetime import datetime
from openpyxl import Workbook, load_workbook
from openpyxl.drawing.image import Image
from openpyxl.styles import Font, colors
import pymysql


class ExcelUtils(object):

    # 构造函数
    def __init__(self):
        self.wb = Workbook() # 得到Excel
        self.ws_one = self.wb.active # 激活表单
        self.ws_one.title = '修改表单' # 修改表单名称
        self.ws_one.sheet_properties.tabColor = '1072BA' # 设置选项卡颜色
        self.ws_two = self.wb.create_sheet('Python表单') # 创建表单2，指定名称创建
        self.ws_three = self.wb.create_sheet() # 创建表单3，默认名称创建


    # 导出Excel
    def do_sth(self):
        # 插入数据
        self.ws_one['A1'] = 66
        self.ws_one['A2'] = 'Hello'
        self.ws_one['A3'] = 'Python'
        self.ws_one['A4'] = datetime.now()

        # 设置多个数据（循环）
        for row in self.ws_two['A1:E5']:
            for cell in row:
                cell.value = 2

        # 对数据进行求和
        self.ws_two['G1'] = '=SUM(A1:E1)'

        # 设置文字样式 sz:字体大小
        FONT = Font(sz=18, color=colors.RED)
        self.ws_one['A2'].font = FONT

        # 插入图片
        # IMG = Image('./static/logo.png')
        # self.ws_one.add_image(IMG, 'B1')

        # 合并单元格
        self.ws_one.merge_cells('A4:E5')
        # 取消合并单元格
        self.ws_one.unmerge_cells('A4:E5')

        self.wb.save('./static/test.xlsx') # 导出表单，参数为指定位置


    # 读取 Excel
    def read_xls(self):
        # 读取 Excel
        ws = load_workbook('./static/template.xlsx')

        # 获取表单
        sheet_names = ws.sheetnames
        print(sheet_names)

        # 获取数据
        # wb = ws.active # 获取默认激活的表单
        # wb = ws['北京大学统计'] # 通过名称获取表单1
        wb = ws[sheet_names[0]]  # 通过名称获取表单2
        for row in wb.rows:
            for cell in row:
                print(cell.value)


    # 获取 mysql 的连接
    def get_conn(self):
        # 连接数据库
        try:
            db = pymysql.Connect(
                host='localhost',
                port=3306,
                user='root',
                passwd='wjw1996321..',
                db='grade',
                charset='utf8'
            )
            return db
        except:
            print('数据库连接异常')



    # 读取Excel并进行操作
    def read_xls_do_sth(self):
        # 读取 Excel
        ws = load_workbook('./static/template.xlsx')

        # 获取表单
        sheet_names = ws.sheetnames
        print(sheet_names)
        wb = ws[sheet_names[0]]

        # 获取数据库
        db = self.get_conn()

        for (i, row) in enumerate(wb.rows):
            if i < 2:
                continue
            # 获取表单数据
            year = wb['A{0}'.format(i + 1)].value
            max = wb['B{0}'.format(i + 1)].value
            avg = wb['C{0}'.format(i + 1)].value

            if year is None:
                continue

            # 操作数据库
            cur = db.cursor() # 获取游标
            # 需要执行的SQL语句
            SQL = "INSERT INTO `user_grade`(`year`, `max`, `avg`) VALUE({year}, {max}, {avg})".format(year=year, max=max, avg=avg)
            cur.execute(SQL)  # 执行SQL语句
            db.commit()  # 提交至数据库

        db.close()  # 关闭连接


    # 从 mysql 导出 Excel
    def mysql_export_xls(self):
        # 数据库操作
        db = self.get_conn() # 获取数据库
        cur = db.cursor() # 获取游标
        SQL = 'SELECT `year`, `max`, `avg` FROM `user_grade`' # 需要执行的SQL语句(如果数据量大，需要借助分页)
        cur.execute(SQL) # 执行SQL语句
        rows = cur.fetchall() # 获取查询的值

        # 操作Excel
        wb = Workbook()
        ws = wb.active

        for (i, row) in enumerate(rows):
            # 传统方式
            # ws['A{0}'.format(i+1)] = row[0]
            # ws['B{0}'.format(i+1)] = row[1]
            # ws['C{0}'.format(i+1)] = row[2]
            # 高级方式
            (ws['A{0}'.format(i+1)], ws['B{0}'.format(i+1)], ws['C{0}'.format(i+1)]) = row

        wb.save('./static/mysql.xlsx')

# 执行的方法
if __name__ == '__main__':
    client = ExcelUtils()
    # client.do_sth()
    # client.read_xls()
    # client.read_xls_do_sth()
    client.mysql_export_xls()
